/* 
 *  Copyright 2012 CodeMagi, Inc.
 * 
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */
package com.codemagi.database;

import com.codemagi.util.*;
import java.sql.*;
import java.util.*;
import org.apache.log4j.*;

/**
 * This bean abstracts Database access without depending on any third party java
 * packages other than the JDBC driver in question.
 *
 * It extends the functionality of DataBean to enable the use of prepared
 * statements. However, multiple statement transactions are no longer supported.
 *
 * NOTE: Non-SELECT SQL with this class has not been tested.
 */
public class PreparedDataBean {

    // display debug output
    protected Logger log = Logger.getLogger(this.getClass());
    
    // Used to figure out if the query is a select 
    protected static final String SELECT = "SELECT";
    
    // The PreparedStatement to execute
    protected PreparedStatement stmt = null;
    
    // The JDBC result set
    protected ResultSet resultSet = null;
    
    // The JDBC result's meta data 
    protected ResultSetMetaData resultSetMetaData = null;
    
    // Connection arguments for the db connection. 
    protected Properties connArgs = new Properties();
    
    // The JDBC connection string. 
    protected String connectString = null;
    
    // The db query.
    protected String query = null;
    
    // Parameters to bind to the query
    protected List<Object> params = new ArrayList<Object>();
    
    // The result set of the db query represented in a 2 dimensional array. 
    protected String[][] twoDArray = null;
    
    // The number or rows in the result rowset. 
    protected int numRows = 0;
    
    // The number of columns in the resultSet
    protected int numCols = 0;
    
    // The current row number of the result set
    private int currentRow = -1;
    
    /**
     * The maximum number of rows in the result set (equivalent to SQL LIMIT).
     * Default: no limit
     */
    protected int limit = -1;
    
    // The maximum number of rows that the result set will contain.
    private static final int MAX_ROWS = Integer.MAX_VALUE;
    
    // Hashtable of result set column names 
    protected LinkedHashMap<String, Integer> columnNames = null;
    
    // boolean to determine whether to return null or "" if a column is empty
    private boolean returnNulls = true;
    
    //determines whether to output SQL statements (to System.out) as they are executed
    protected boolean showSql = false;
    
    //determines whether to output timing info (to System.out)
    protected boolean showTiming = false;
    
    //The database connection  
    protected Connection conn = null;

    /**
     * standard constructor
     *
     * @param user database user
     * @param password database password
     * @param connectString standard JDBC compliant database connect string
     * @param driver JDBC Driver path
     */
    public PreparedDataBean(String user, String password, String connectString, String driver) {
	log.debug("Constructing PreparedDataBean");
	setDbParams(user, password, connectString, driver);
    }

    
    /**
     * Convenience constructor takes a Properties object containing the user, password, 
     * connect string and driver. 
     */
    public PreparedDataBean(Properties dbProperties) {

	String user    = dbProperties.getProperty("user");
	String pass    = dbProperties.getProperty("password");
	String connect = dbProperties.getProperty("connectString");
	String driver  = dbProperties.getProperty("driver");

	setDbParams(user, pass, connect, driver);
    }

    
    /**
     * Null constructor required for Bean-ness
     */
    public PreparedDataBean() {
	//nada
    }

    
    /**
     * Sets any necessary DB params for this PreparedDataBean object
     */
    public void setDbParams(String user, String pass, String connect, String driver) {

	//check required fields
	String[] requiredFields = { user, pass, connect, driver };
	if ( Utils.isEmpty(requiredFields) ) {
	    System.out.println("WARNING: PreparedDataBean config data missing");
	}

	try {
	    // load DB driver 
	    Class.forName(driver);
	} catch (Exception e) {
	    log.warn("WARNING: PreparedDataBean could not load driver: " + driver, e);
	}

	connArgs.setProperty("user", user);
	connArgs.setProperty("password", pass);

	this.connectString = connect;
    }

    
    /**
     * Determines whether or not to return null from getItem methods. 
     * If set to true, nulls will be returned if there was no value in the column. 
     * If set to false, "" will be returned instead.
     *
     * @param newValue   boolean to determine whether or not to return nulls
     */
    public void setReturnNulls(boolean newValue) {
	returnNulls = newValue;
    }


    /**
     * Determines whether to output SQL statements to System.out as they are executed
     *
     * @param newValue    If true, SQL statements will be written to System.out as executed
     *
     * @deprecated        Use setLogger to output SQL statements to a log4j Logger instead
     */
    public void setShowSql(boolean newValue) {
	showSql = newValue;
    }


    /**
     * Determines whether to output timing info to the log4j Logger
     *
     * @param newValue    If true, timing info will be written to the Logger as executed
     */
    public void setShowTiming(boolean newValue) {
	showTiming = newValue;
    }


    /**
     * Sets the limit for the number of rows returned. Default behavior is no limit.
     *
     * @param newValue    Maximum number of rows to return in the result set (set to -1 for no limit)
     */
    public void setLimit(int newValue) {
	limit = newValue;
    }


    /**
     * Sets a different log4j Logger than the default one.
     */
    public void setLogger(Logger newValue) {
	log = newValue;
    }


    /**
     * Execute the query. Throw an exception is bad things happen.
     *
     * @throws SQLException when bad things happen
     */
    public void executeQuery() {
	
	log.debug("PreparedDatabean.executeQuery()");

	//output SQL if we have been instructed to do so
	if (showSql) System.out.println(query);
	log.info(query);

	long startTime    = System.currentTimeMillis();
	long intervalTime = startTime;

	//reset class-level variables
        resultSet         = null;
	resultSetMetaData = null;
	twoDArray         = null;
	numRows           = 0;
	numCols           = 0;

	// Get a connection
	try {
	    openConnection();
	    conn.setAutoCommit(true);

	    if (showTiming) {
		intervalTime = System.currentTimeMillis();
		log.info("Timing: got connection in: " + (intervalTime - startTime));
	    }

	    log.debug("CONN: creating statement");
	    stmt = conn.prepareStatement(query);
	    log.debug("CONN: done creating statement");

	    if (stmt == null) {
		log.fatal("ERROR: unable to execute query: PreparedStatement is null");
		return; // no sense to continue without a query
	    }
    
	    //bind parameters to prepared statement
	    int paramIndex = 1;
	    for (Object o : params) {
		stmt.setObject(paramIndex, o);
		paramIndex++;
	    }

	    log.debug("CONN: setting max rows");
	    try {
		stmt.setMaxRows(MAX_ROWS);
	    } catch (Exception smr_E) {
		// Not all databases support this, but try anyways	
	    }
	    log.debug("CONN: done setting max rows");

	    // if the query is a select statement, get the results
	    if (query.toUpperCase().trim().indexOf(SELECT) == 0) {
		log.debug("CONN: SQL is a SELECT statement");

		if (showTiming) {
		    log.info("Timing: ready to exec query in: " + (System.currentTimeMillis() - intervalTime));
		    intervalTime = System.currentTimeMillis();
		}

		resultSet = stmt.executeQuery();
		log.debug("CONN: query complete");

		if (showTiming) {
		    log.info("Timing: query executed in: " + (System.currentTimeMillis() - intervalTime));
		    intervalTime = System.currentTimeMillis();
		}

		resultSetMetaData = resultSet.getMetaData();
		log.debug("CONN: result set meta data Complete");

		if (showTiming) {
		    log.info("Timing: got meta data in: " + (System.currentTimeMillis() - intervalTime));
		    intervalTime = System.currentTimeMillis();
		}

		// build the column-name-to-column-num mapping 
		buildColumnNameToNumberMap();
		log.debug("CONN: column name map complete");

		if (showTiming) {
		    log.info("Timing: built column name map in: " + (System.currentTimeMillis() - intervalTime));
		    intervalTime = System.currentTimeMillis();
		}

		// fill the data array 
		create2DArray();
		log.debug("CONN: 2-d array Complete");

		if (showTiming) {
		    log.info("Timing: built cache in: " + (System.currentTimeMillis() - intervalTime));
		    intervalTime = System.currentTimeMillis();
		}

	    } else {
		//query is NOT a select, so execute update 
		log.debug("CONN: SQL is NOT a SELECT statement");
		int numRows = stmt.executeUpdate();
		log.debug("      Update executed. " + numRows + " rows updated");
	    }

	} catch (java.sql.SQLException jsqle) {

	    log.debug("Message:   " + jsqle.getMessage());
	    log.debug("SQLState:  " + jsqle.getSQLState());
	    log.debug("ErrorCode: " + jsqle.getErrorCode());
	    log.debug("", jsqle);

	} finally {
	    //close ResultSet
	    try {
		if (resultSet != null) resultSet.close();
		log.debug("CONN: ResultSet closed");
	    } catch (SQLException sqle) {
		log.debug("Could not close ResultSet", sqle);
	    }

	    //close Statement
	    try {
		if (stmt != null) stmt.close();
		log.debug("CONN: Statement closed");
	    } catch (SQLException sqle) {
		log.debug("Could not close Statement", sqle);
	    }

	    //close all DB connections
	    closeConnection();
	}

	if (showTiming) {
	    log.info("Timing: query complete in total: " + (System.currentTimeMillis() - startTime));
	}

    }


    /**
     * Opens the JDBC database connection
     */
    protected void openConnection() throws java.sql.SQLException {

	//if an explicit connection has already been set, use that
	if (conn != null && !conn.isClosed()) return;

	log.debug("USER: " + connArgs.getProperty("user"));
	log.debug("PASS: " + connArgs.getProperty("password"));
	log.debug("CONN: " + connectString);

	conn = DriverManager.getConnection(connectString, connArgs);
	log.debug("CONN: Connection Complete");
    }


    /**
     * Closes the database connection if it is open
     */
    protected void closeConnection() {
	if ( conn != null ) {
	    try {
		conn.close();
		log.debug("CONN: Connection closed");

		conn = null;

	    } catch (SQLException sqle) {
		log.debug("Could not close Connection", sqle);
	    }
	}
    }


    /**
     * Explicitly set the connection to be used.
     *
     * Note: Calling executeQuery will close and nullify this connection
     */
    public void setConnection(Connection newValue) {
	conn = newValue;
    }


    /**
     * Finalize method overridden to ensure that the database connection is closed
     */
    protected void finalize() throws Throwable {

	//close DB connection
	closeConnection();

    }


    /**
     * Create a 2D array of Strings from the JDBC result set.
     *
     * @return The 2 dimensional array representing the result set.
     */
    protected void create2DArray() {

	//make sure we set the class-level current row back 
	setCurrentRow(-1);

	int increment = 10;
	int currentRow = 0;

	//get the number of columns inthe result set
	setNumCols();
	int numCols = getNumCols();

	//estimate the number of rows in the result set
	int estNumRows = estimateNumRows();
        int arraySize  = estNumRows;

        String[][] ret = new String[ estNumRows ][ numCols ];
        String[][] temp = new String[ 1 ][ 1 ];
	String buf;

        while ( nextResSetRow() && (currentRow < limit || limit < 0) ) {

	    try {

                if ( currentRow < arraySize ) {

		    // copy the current row into the return array
                    for ( int col = 0; col < numCols; col++ ) {
                        ret[ currentRow ][ col ] = getRowSetItem( col + 1 );
		    }
		} else {

		    if (true) System.out.println("dataBean: reallocating 2d array");

		    // copy contents of ret to temp
                    temp = new String[ currentRow + 1 ][ numCols + 1 ];
                    System.arraycopy( ret, 0, temp, 0, currentRow );

		    // We need to re-allocate the array here                
		    arraySize = currentRow + increment;
                    ret = new String[ arraySize ][ numCols + 1 ];
                    System.arraycopy( temp, 0, ret, 0, currentRow );

		    // copy the current row into the return array
                    for ( int col = 0; col < numCols; col++ ) {
                        ret[ currentRow ][ col ] = getRowSetItem( col + 1 );
		    }
		}
            } catch ( ArrayIndexOutOfBoundsException e ) {
		// nothing on purpose
	    }

	    currentRow++;
	}

	numRows = currentRow;

	twoDArray = ret;
    }

    
    /**
     * Get the item for specified column in the current row of the rowset
     * of data.
     *
     * @param col The column to get the data for.
     *
     * @return A String representing the data, or null if an error occurs.
     */
    protected String getRowSetItem( int col ) {
	String buf = null;

	try {
	    String dataType = resultSetMetaData.getColumnTypeName(col);
	    if ("CLOB".equals(dataType)) {
		Clob item = resultSet.getClob(col);
		if (item != null) {
		    buf = item.getSubString(1l, (int)item.length());
		} else {
		    buf = null;
		}
	    } else {
		buf = resultSet.getString( col );
	    }

        } catch ( java.sql.SQLException sqle ) {
	    log.debug("", sqle);
	}

	return buf;
    }

    
    /**
     * Move the current pointer to the next row in the result set.
     *
     * @return true if the pointer is moved successfully, false if there are no more rows in the result set.
     */
    protected boolean nextResSetRow() {
	boolean ret = false;
	try {
	    ret = resultSet.next();
        } catch ( Exception e ) {
	    // nothing on purpose
	    log.debug("", e);
	}

        return( ret );
    }


    /**
     * Returns the number of rows in the query's result data.
     *
     * @return int   The number of rows in the result data.
     */
    public int getNumRows() {
	return numRows;
    }


    /**
     * Returns the number of columns in the result set.
     *
     * @return int   The number of columns in the result data.
     */
    public int getNumCols() {
	return numCols;
    }


    /**
     * Sets the number of columns in the query's result data.
     */
    protected void setNumCols() {
	log.debug("setNumCols() called with: " + resultSetMetaData.toString());

	try {
	    log.debug("setNumCols(): " + resultSetMetaData.getColumnCount());
	    numCols = resultSetMetaData.getColumnCount();
        } catch ( java.sql.SQLException jsqle ) {
	    log.debug("", jsqle);
	}
    }

    
    /**
     * Estimates the number of rows in the result set. NOTE: Not all databases/drivers support this,
     * so the default value is 10 if the real value cannot be found.                                                       
     *
     * @return int   The estimated number of rows in the result set.   
     */
    protected int estimateNumRows() {
	int output = 10;

	try {
	    resultSet.last();
	    output = resultSet.getRow();
	    resultSet.beforeFirst();
	} catch (Exception e) {
	    log.debug("", e);
	}

	return output;
    }

    
    /**
     * Get the connect string that is used to connect to the db.
     *
     * @return The connect string.
     */
    public String getConnectString() {
        return( connectString );
    }

    
    /**
     * Get the specified item (row, col) from the 2D array representation
     * of the result set.
     *
     * @param row       The row of the item to get.
     * @param col       The column of the item to get.
     *
     * @return String   The item if row and col are legal values, null otherwise
     */
    public String getItem( int row, int col ) {

	String ret = null;

        if ( row < getNumRows() && col < getNumCols() && twoDArray != null ) {   //numCols is 1 based, twoDArray is zero based
            ret = twoDArray[ row ][ col ];
	}

	//if returnNulls is false, convert null values to ""
	if ( ret == null && !returnNulls) ret = "";

	return ret;
    }


    /**
     * Gets the specified item (row, columnName) from the 2D array representation 
     * of the result set
     *
     * @param row         The row number of the item to get
     * @param columnName  The name of the column, as specified in the SQL query
     *
     * @return String     The item if row and col are legal values, null otherwise
     */
    public String getItem( int row, String columnName) {

	//get column number from map
	int col = getColumnNumber(columnName);

	return getItem(row, col);
    }


    /**
     * Gets the specified item (currentRow, column) from the 2D array representation 
     * of the result set. Used for iterating through a result set row by row.
     *
     * @param columnName  The name of the column, as specified in the SQL query
     *
     * @return The item if row and col are legal values, null otherwise
     *
     * @see nextRow()
     */
    public String getItem( String columnName ) {
	return getItem(currentRow, columnName);
    }


    /**
     * Gets the specified item (currentRow, column) from the 2D array representation 
     * of the result set. Used for iterating through a result set row by row.
     *
     * @param col         The column number of the item to get
     *
     * @return String     The item if row and col are legal values, null otherwise
     *
     * @see nextRow()
     */
    public String getItem( int col ) {
	return getItem(currentRow, col);
    }


    /**
     * Increments the current row. Used for iterating through a result set row by row.
     */
    public void nextRow() {
	currentRow++;
    }


    /**
     * Sets class-level currentRow to the input value
     *
     * @param newValue     Value to set currentRow to                                                     
     */
    protected void setCurrentRow(int newValue) {
	currentRow = newValue;
    }


    /**
     * Returns true if there are more rows in the result set
     *
     * @return boolean   true if there are more rows remaining in the result set (ie, numRows > currentRow)
     */
    public boolean hasMoreRows() {
	if (getNumRows() > currentRow + 1) return true;

	return false;
    }


    /**
     * Gets the specified item from the NEXT row (currentRow+1, column) of the 2D array representation 
     * of the result set. Used for iterating through a result set row by row. 
     *
     * @param columnName  The name of the column, as specified in the SQL query  
     *
     * @return String     The item if row and col are legal values, null otherwise  
     *
     * @see nextRow()
     */
    public String getNextItem( String columnName ) {
        return getItem(currentRow+1, columnName);
    }


    /**
     * returns the number of the current row
     */
    public int getRowNum() {
	return currentRow;
    }

    
    /**
     * Returns the column number (zero-based) for the specified column name
     *
     * @param columnName     The name of the column
     * @return int           The column's number in the result set
     */
    private int getColumnNumber(String columnName) {

	//prevent NullPointerException
    	if ( Utils.isEmpty(columnName) || columnNames == null) return 0;

    	Integer theInt = (Integer)columnNames.get(columnName.toUpperCase());

    	if (theInt == null) return 0 ;  //changed from -1 for fault tolerance
    	
	return theInt.intValue();
    }

    
    /**
     * Builds a Hashtable of column names to column numbers. First column is column 0, second is column 1, etc...
     */
    private void buildColumnNameToNumberMap() throws SQLException {

	int numCols = resultSetMetaData.getColumnCount();

	columnNames = new LinkedHashMap<String, Integer>();

	log.debug("buildColumnNameToNumberMap(): COLS: " + numCols);

	for (int x = 1; x <= numCols; x++) {
	    log.debug("building column-map: " + x + "=" + resultSetMetaData.getColumnName(x));
	    columnNames.put(resultSetMetaData.getColumnName(x).toUpperCase(), new Integer(x-1));
	}
    }

    /**
     * Returns a Set of Column names returned by the query
     */
    public Set<String> getColumnNames() {
	if (columnNames != null) {
	    return columnNames.keySet();
	}
	
	return new HashSet<String>();
    }

    /**
     * Set the query to be executed by this bean.
     *
     * @param query The query to be executed by this bean.
     */
    public void setQuery(String query) {
	this.query = query;
	this.params = new ArrayList<Object>();
    }

    
    /**
     * Overridden version of setQuery that accepts a StringBuffer argument.
     */
    public void setQuery(StringBuffer query) {
	if (query == null) return;

	setQuery(query.toString());
    }

    
    /**
     * Get the current query that is executed by this bean.
     *
     * @return the query that is executed by this bean.
     */
    public String getQuery() {
	return query;
    }


    /**
     * Binds a parameter to the prepared statement
     */
    public void bind(Object o) {
	params.add(o);
	log.debug("bind() o: " + o + " paramIndex: " + params.size());
    }

}
